#!/usr/bin/python

import sys
import datetime
#import MySQLdb
import sqlite3 as lite
import json
import os
import shutil
import math

class Interface:
    def __init__(self):
        pass

    def SQupdateData (self,paras):
        con = None
        domain = paras[0]
        domain = 'www.'+domain+'/'
        start = paras[1][0]
        end = paras[1][1]
        
        
        
        home = os.getenv("HOME")        
        data=open(home+'/fire.log','r')
        #data = json.load(json_data)
        #pprint(data)
        buff = []
        for line in data.readlines():
            
            tmp=line.split("\r\n")
            buff += tmp
        #print buff
        
        
        #Save to backup log and delete the new.
        data_to_append = open(home+'/fire.log','r')
        FILE=open(home+'/fire_backup.log',"a")
        FILE.write(data_to_append.read())
        FILE.close()
        #os.remove(home+'/fire.log')
        
        try:
    
            con = lite.connect('test.db')
            cur = con.cursor()    
          
            log_reason = []
            ff_version = []
            method = []
            uri = []
            host = []
            request_event_ts = []
            content_type = []
            content_length = []
            AcceptEncoding = []
            ContentEncoding = []
        
            content_load_ts = []
            load_ts = []
            session_abort_ts = []
            server_cnxs = []
            server_os = []
            server_http_v = []
            http_id = []
            session_start = []
            session_url = []
            if_complete_cache = []
        
            localAddress = []
            localPort = []
            remoteAddress = []
            remotePort = []
            response_code = []
            http_request_bytes = []
            http_header_bytes = []
            http_body_bytes = []
            http_cache_bytes = []
            dns_start =[]
        
            dns_end = []
            dns_time = []
            syn_start =  []
            syn_end = []
            tcp_cnxting = []
            GET_sent = []
            First_Bytes = []
            app_rtt = []
            EndTime = []
            data_trans =[]    #40
         
            full_load_time = []
            content_load_time = []
            abort_time = []
            first_annoy = []
            nr_annoying = []
            tabId = []
            client_ip = []
            Referer = []
            curr_wifi_quality = []
            curr_wifi_quality2 =[]
        
            cpu_idle = []
            cpu_idle2 = []
            cpu_percent_ffx = []
            cpu_percent_ffx2 = []
            mem_free = []
            mem_free2 = []
            mem_used = []
            mem_used2 = []
            mem_percent_ffx = []
            mem_percent_ffx2 =[]    #60
        
            ping_gw = []
            ping_gw2 = []
            ping_dns = []
            ping_dns2 = []
            ping_google = []
            ping_google2 = []
            obj_aborted = []
            location = []
            client_os = []
            clientID =[]
        
            cmt =[]
        
            print "Updating database..."
        
            sql_create = """CREATE TABLE IF NOT EXISTS example (
                            id INTEGER PRIMARY KEY AUTOINCREMENT,
                                                  log_reason VARCHAR(100),
                                                  ff_version VARCHAR(100),
                                                  method VARCHAR(100),
                                                  uri VARCHAR(500),
                                                  host VARCHAR(100),
                                                  request_event_ts VARCHAR(100),
                                                  content_type VARCHAR(100),
                                                  content_length VARCHAR(100),
                                                  AcceptEncoding VARCHAR(100),
                                                  ContentEncoding VARCHAR(100),
         
                                                  content_load_ts VARCHAR(100),
                                                  load_ts VARCHAR(100),
                                                  session_abort_ts VARCHAR(100),
                                                  server_cnxs VARCHAR(100),
                                                  server_os VARCHAR(100),
                                                  server_http_v VARCHAR(100),
                                                  http_id VARCHAR(100),
                                                  session_start VARCHAR(100),
                                                  session_url VARCHAR(500),
                                                  if_complete_cache VARCHAR(100),
         
                                                  localAddress VARCHAR(100),
                                                  localPort VARCHAR(100),
                                                  remoteAddress VARCHAR(100),
                                                  remotePort VARCHAR(100),
                                                  response_code VARCHAR(100),
                                                  http_request_bytes VARCHAR(100),
                                                  http_header_bytes VARCHAR(100),
                                                  http_body_bytes VARCHAR(100),
                                                  http_cache_bytes VARCHAR(100),
                                                  dns_start VARCHAR(100),
         
                                                  dns_end VARCHAR(100),
                                                  dns_time VARCHAR(100),
                                                  syn_start VARCHAR(100),
                                                  syn_end VARCHAR(100),
                                                  tcp_cnxting VARCHAR(100),
                                                  GET_sent VARCHAR(100),
                                                  First_Bytes VARCHAR(100),
                                                  app_rtt VARCHAR(100),
                                                  EndTime VARCHAR(100),
                                                  data_trans VARCHAR(100),
                                                  
                                                  full_load_time VARCHAR(100),
                                                  content_load_time VARCHAR(100),
                                                  abort_time VARCHAR(100),
                                                  first_annoy VARCHAR(100),
                                                  nr_annoying VARCHAR(100),
                                                  tabId VARCHAR(100),
                                                  client_ip VARCHAR(100),
                                                  Referer VARCHAR(500),
                                                  curr_wifi_quality VARCHAR(100),
                                                  curr_wifi_quality2 VARCHAR(100),
                                                  
                                                  cpu_idle VARCHAR(100),
                                                  cpu_idle2 VARCHAR(100),
                                                  cpu_percent_ffx VARCHAR(100),
                                                  cpu_percent_ffx2 VARCHAR(100),
                                                  mem_free VARCHAR(100),
                                                  mem_free2 VARCHAR(100),
                                                  mem_used VARCHAR(100),
                                                  mem_used2 VARCHAR(100),
                                                  mem_percent_ffx VARCHAR(100),
                                                  mem_percent_ffx2 VARCHAR(100),
                                                  
                                                  ping_gw VARCHAR(100),
                                                  ping_gw2 VARCHAR(100),
                                                  ping_dns VARCHAR(100),
                                                  ping_dns2 VARCHAR(100),
                                                  ping_google VARCHAR(100),
                                                  ping_google2 VARCHAR(100),
                                                  obj_aborted VARCHAR(100),
                                                  location VARCHAR(100),
                                                  client_os VARCHAR(100),
                                                  clientID VARCHAR(100),
                                                  
                                                  cmt VARCHAR(200)
                              );"""
       
            cur.execute(sql_create)
            con.commit()
        
            json_obj = buff
        
        
        
        
        
            i=0
            for obj in json_obj:
                    try:
                        print len(json_obj)
                        doc = json.loads(obj, encoding = 'utf-8')
                        
                        # Parsing
                        log_reason.append(doc["log_reason"].encode('utf-8'))
                        ff_version.append(doc["ff_version"].encode('utf-8'))
                        method.append(doc["method"].encode('utf-8'))
                        uri.append(doc["uri"].encode('utf-8'))
                        host.append(doc["host"].encode('utf-8'))
                        request_event_ts.append(doc["request_event_ts"].encode('utf-8'))
                        content_type.append(doc["content_type"].encode('utf-8'))
                        content_length.append(doc["content_length"].encode('utf-8'))
                        AcceptEncoding.append(doc["AcceptEncoding"].encode('utf-8'))
                        ContentEncoding.append(doc["ContentEncoding"].encode('utf-8'))  #10
                        
                        content_load_ts.append(doc["content_load_ts"].encode('utf-8'))
                        load_ts.append(doc["load_ts"].encode('utf-8'))
                        session_abort_ts.append(doc["session_abort_ts"].encode('utf-8'))
                        server_cnxs.append(doc["server_cnxs"].encode('utf-8'))
                        server_os.append(doc["server_os"].encode('utf-8'))
                        server_http_v.append(doc["server_http_v"].encode('utf-8'))
                        http_id.append(doc["http_id"].encode('utf-8'))
                        session_start.append(doc["session_start"].encode('utf-8'))
                        session_url.append(doc["session_url"].encode('utf-8'))
                        if_complete_cache.append(doc["if_complete_cache"].encode('utf-8')) #20
                        
                        localAddress.append(doc["localAddress"].encode('utf-8'))
                        localPort.append(doc["localPort"].encode('utf-8'))
                        remoteAddress.append(doc["remoteAddress"].encode('utf-8'))
                        remotePort.append(doc["remotePort"].encode('utf-8'))
                        response_code.append(doc["response_code"].encode('utf-8'))
                        http_request_bytes.append(doc["http_request_bytes"].encode('utf-8'))
                        http_header_bytes.append(doc["http_header_bytes"].encode('utf-8'))
                        http_body_bytes.append(doc["http_body_bytes"].encode('utf-8'))
                        http_cache_bytes.append(doc["http_cache_bytes"].encode('utf-8'))
                        dns_start.append(doc["dns_start"].encode('utf-8')) #30
                        
                        dns_end.append(doc["dns_end"].encode('utf-8'))
                        dns_time.append(doc["dns_time"].encode('utf-8'))
                        syn_start.append(doc["syn_start"].encode('utf-8'))
                        syn_end.append(doc["syn_end"].encode('utf-8'))
                        tcp_cnxting.append(doc["tcp_cnxting"].encode('utf-8'))
                        GET_sent.append(doc["GET_sent"].encode('utf-8'))
                        First_Bytes.append(doc["First_Bytes"].encode('utf-8'))
                        app_rtt.append(doc["app_rtt"].encode('utf-8'))
                        EndTime.append(doc["EndTime"].encode('utf-8'))
                        data_trans.append(doc["data_trans"].encode('utf-8')) #40
                        
                        full_load_time.append(doc["full_load_time"].encode('utf-8'))
                        content_load_time.append(doc["content_load_time"].encode('utf-8'))
                        abort_time.append(doc["abort_time"].encode('utf-8'))
                        first_annoy.append(doc["first_annoy"].encode('utf-8'))
                        nr_annoying.append(doc["nr_annoying"].encode('utf-8'))
                        tabId.append(doc["tabId"].encode('utf-8'))
                        client_ip.append(doc["client_ip"].encode('utf-8'))
                        Referer.append(doc["Referer"].encode('utf-8'))
                        curr_wifi_quality.append(doc["curr_wifi_quality"].encode('utf-8'))
                        curr_wifi_quality2.append(doc["curr_wifi_quality2"].encode('utf-8'))  #50
                        
                        
                        cpu_idle.append(doc["cpu_idle"].encode('utf-8'))
                        cpu_idle2.append(doc["cpu_idle2"].encode('utf-8'))
                        cpu_percent_ffx.append(doc["cpu_percent_ffx"].encode('utf-8'))
                        cpu_percent_ffx2.append(doc["cpu_percent_ffx2"].encode('utf-8'))
                        mem_free.append(doc["mem_free"].encode('utf-8'))
                        mem_free2.append(doc["mem_free2"].encode('utf-8'))
                        mem_used.append(doc["mem_used"].encode('utf-8'))
                        mem_used2.append(doc["mem_used2"].encode('utf-8'))
                        mem_percent_ffx.append(doc["mem_percent_ffx"].encode('utf-8'))
                        mem_percent_ffx2.append(doc["mem_percent_ffx2"].encode('utf-8'))  #60
                        
                        
                        ping_gw.append(doc["ping_gw"].encode('utf-8'))
                        ping_gw2.append(doc["ping_gw2"].encode('utf-8'))
                        ping_dns.append(doc["ping_dns"].encode('utf-8'))
                        ping_dns2.append(doc["ping_dns2"].encode('utf-8'))
                        ping_google.append(doc["ping_google"].encode('utf-8'))
                        ping_google2.append(doc["ping_google2"].encode('utf-8'))
                        obj_aborted.append(doc["obj_aborted"].encode('utf-8'))
                        location.append(doc["location"].encode('utf-8'))
                        client_os.append(doc["client_os"].encode('utf-8'))
                        clientID.append(doc["clientID"].encode('utf-8'))  #70
                        
                        if doc["cmt"] != None:
                            cmt.append(doc["cmt"].encode('utf-8'))
                        else:
                            cmt.append("No comment")
                        
                        
                        
                        
                        
                        sql = """insert into example (
                                 log_reason,ff_version,method,uri,host,request_event_ts,content_type,content_length,AcceptEncoding,ContentEncoding,
                                 content_load_ts,load_ts,session_abort_ts,server_cnxs,server_os,server_http_v,http_id,session_start,session_url,if_complete_cache,
                                 localAddress,localPort,remoteAddress,remotePort,response_code,http_request_bytes,http_header_bytes,http_body_bytes,http_cache_bytes,dns_start,
                                 dns_end,dns_time,syn_start,syn_end,tcp_cnxting,GET_sent,First_Bytes,app_rtt,EndTime,data_trans,
                                 full_load_time,content_load_time,abort_time,first_annoy,nr_annoying,tabId,client_ip,Referer,curr_wifi_quality,curr_wifi_quality2,
                                 cpu_idle,cpu_idle2,cpu_percent_ffx,cpu_percent_ffx2,mem_free,mem_free2,mem_used,mem_used2,mem_percent_ffx,mem_percent_ffx2,
                                 ping_gw,ping_gw2,ping_dns,ping_dns2,ping_google,ping_google2,obj_aborted,location,client_os,clientID,
                                 cmt
        
                                 ) 
                                 values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',
                                         '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',
                                         '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',
                                         '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',
                                         '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',
                                         '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',
                                         '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');""" %(
                                 log_reason[i],ff_version[i],method[i],uri[i],host[i],request_event_ts[i],content_type[i],
                                 content_length[i],AcceptEncoding[i],ContentEncoding[i],
                                 
                                 content_load_ts[i],load_ts[i],session_abort_ts[i],server_cnxs[i],server_os[i],server_http_v[i],
                                 http_id[i],session_start[i],session_url[i],if_complete_cache[i],
                                 
                                 localAddress[i],localPort[i],remoteAddress[i],remotePort[i],response_code[i],
                                 http_request_bytes[i],http_header_bytes[i],http_body_bytes[i],http_cache_bytes[i],dns_start[i],
                                 
                                 dns_end[i],dns_time[i],syn_start[i],syn_end[i],tcp_cnxting[i],
                                 GET_sent[i],First_Bytes[i],app_rtt[i],EndTime[i],data_trans[i],
                                 
                                 full_load_time[i],content_load_time[i],abort_time[i],first_annoy[i],nr_annoying[i],
                                 tabId[i],client_ip[i],Referer[i],curr_wifi_quality[i],curr_wifi_quality2[i],
                                 
                                 cpu_idle[i],cpu_idle2[i],cpu_percent_ffx[i],cpu_percent_ffx2[i],mem_free[i],
                                 mem_free2[i],mem_used[i],mem_used2[i],mem_percent_ffx[i],mem_percent_ffx2[i],
                                 
                                 ping_gw[i],ping_gw2[i],ping_dns[i],ping_dns2[i],ping_google[i],
                                 ping_google2[i],obj_aborted[i],location[i],client_os[i],clientID[i],
                                 
                                 cmt[i]
                                 
                                 )
                        
                        #print sql
                        i=i+1
                        cur.execute(sql)
                        con.commit()
                        
                    except Exception as json_ex:
                        print "JSON Exception occurred: %s" % str(json_ex)
                        continue
        
            print "Succefully updated"            
            
            
            
            #data = cur.fetchall()
      
            #print "SQLite version: %s" % data                
      
        except lite.Error, e:
      
            print "Error %s:" % e.args[0]
            #sys.exit(1)
    
        finally:
    
            if con:
                con.close()
    
    def SQqueryData (self, paras):
    # paras = [String domain, [datetime.datetime start, datetime.datetime end]]
        #con = None
        
        try:
            home = os.getenv("HOME")        
            
            con = lite.connect('/datas/xlan/cui/figaro/salvo/data_8dec/napoli_8dec.sqlite')
            
            cur = con.cursor()
        
        
        
            domain = paras[0]
            #domain = 'www.'+domain+'/'
            start = paras[1][0]
            end = paras[1][1]
            log_clustering = paras[2]
        
            #print "Paras ->",domain,start,end
        
            # and session_start<'%s'
        
            if domain == None:
                domain = ''
            sql_select = """SELECT avg_dns,avg_tcp,avg_http,avg_trans,full_load_time,starting_time from 
                    (SELECT t1.session_start, t1.session_url, case when c1-c2=0 then 0.0 else sum1/(c1-c2) end as avg_dns from (SELECT             session_start, session_url, sum(dns) as sum1, count(*) as c1 from (SELECT session_start, session_url, dns_time as dns from raw_tb)t  group by session_start, session_url)t1, (SELECT session_start, session_url, sum(dns) as sum2, count(*) as c2 from (SELECT  session_start, session_url, dns_time as dns from raw_tb)t where dns=0 group by session_start , session_url)t2 where t1.session_start=t2.session_start and t1.session_url=t2.session_url)t1,
                    (SELECT session_start as starting_time, session_url,full_load_time, avg(tcp_cnxting) as avg_tcp from raw_tb  
                    where session_url like '%%%s%%' and EndTime<load_ts and EndTime>'2000-01-01 01:00:00' and tcp_cnxting>0 
                    and session_start>'%s' and session_start<'%s'
                    group by session_start, session_url)t2,
                    (SELECT session_start,session_url, avg(app_rtt) as avg_http, avg(data_trans) as avg_trans from raw_tb  
                    where session_url like '%%%s%%' and EndTime<load_ts and EndTime>'2000-01-01 01:00:00' 
                    and request_event_ts>'2000-01-01 01:00:00' and session_start>'%s' and session_start<'%s'
                    group by session_start, session_url)t3
                    where t1.session_start=t2.starting_time and t1.session_start=t3.session_start and 
                    t2.starting_time=t3.session_start and t1.session_url=t2.session_url and 
                    t1.session_url=t3.session_url and t2.session_url=t3.session_url;"""% (domain,start,end,domain,start,end)
        
            print sql_select
            cur.execute(sql_select)
            
        
            avg_tcp =[]
            avg_dns =[]
            avg_http =[]
            avg_trans=[]
            full_load_time =[]
            session_start =[] 
        
            data_to_fetch = cur.fetchall()
            #print data_to_fetch
            for one in data_to_fetch:
            
                avg_dns.append(one[0])
                avg_tcp.append(one[1])
                avg_http.append(one[2])
                avg_trans.append(one[3])
                full_load_time.append(one[4])
                session_start.append(one[5])
                #avg_tcp.append(data_to_fetch[7])
        
          
            #print "AVG DNS",avg_dns
            #print "AVG TCP",avg_tcp
            #print "AVG HTTP",avg_http
            #print "AVG TRANS",avg_trans
            #print "FULL LOAD TIME",full_load_time
            #print "PAGE LOAD TIME 2",page_load_time2       
        
        
      
            #db.close()
            log_dns = []
            log_tcp = []
            log_http = []
            log_trans = []
            for one in avg_dns:
                log_dns.append(math.log10(one+1))
            for one in avg_tcp:
                log_tcp.append(math.log10(one+1))
            for one in avg_http:
                log_http.append(math.log10(one+1))
            for one in avg_trans:
                log_trans.append(math.log10(one+1))
            #print "Log 10 Based -->",log_dns,log_tcp
            
            #print "log --> ",log_clustering
            self.dolog = log_clustering
            
            if (log_clustering):
                return [log_dns,log_tcp,log_http,log_trans,full_load_time,session_start]
            else:
                return [avg_dns,avg_tcp,avg_http,avg_trans,full_load_time,session_start]
                    
        
        except lite.Error, e:
      
            print "Error %s:" % e.args[0]
            #sys.exit(1)
    
        finally:
    
            if con:
                con.close()    
